package com.ctshk.rpc.ship.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ctshk.rpc.ship.dto.ShipProductCostAndDateListDTO;
import com.ctshk.rpc.ship.dto.ShipProductCostDTO;
import com.ctshk.rpc.ship.entity.ShipProductCost;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 船票产品成本表 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2021-03-05
 */
public interface ShipProductCostMapper extends BaseMapper<ShipProductCost> {

    @Select("<script>" + "SELECT " +
            "t3.id,t3.cost_name as costName,t3.currency_id as currencyId,t3.currency_name as currencyName,t3.exchange_rate exchangeRate,t3.daily_stock as dailyStock " +
            "FROM " +
            " t_ship_product_cost t3  \n" +
            "WHERE t3.is_deleted = 0 " +
            "<if test=\"ProductId != null and ProductId != ''\">AND t3.ship_product_id = #{ProductId}</if> \n" +
            "</script>")
    List<ShipProductCostDTO> getShipProductCost(@Param("ProductId") Long id);

    @Select("<script>" + " SELECT\n" +
            " t2.tourist_type_name touristTypeName,\n" +
            " t2.tourist_type_id touristTypeId,\n" +
            " t2.cost_price costPrice,\n" +
            " t2.cost_code costCode \n" +
            " FROM\n" +
            " t_ship_product_cost t\n" +
            " LEFT JOIN t_ship_product_cost_date t1 ON t.id = t1.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t2 ON t.id = t2.cost_id \n" +
            "WHERE 1=1 \n" +
            "<if test=\"id != null and id != ''\">AND t.ship_product_id =  #{id}</if> \n" +
            "<if test=\"localDate != null \"> " +
            " and t1.date_begin &lt;= #{localDate} and t1.date_end &gt;= #{localDate} </if> \n" +
            "</script>")
    List<ShipProductCostAndDateListDTO> queryShipProductCostAndDateList(@Param("id") Long id, @Param("localDate") LocalDate localDate);

    @Select("<script>" + " SELECT " +
            " t4.tourist_type_name touristTypeName," +
            " t4.tourist_type_id touristTypeId," +
            " t2.currency_name currencyName," +
            " t4.cost_price costPrice,\n" +
            " (CASE " +
            " t10.adjust_type " +
            " WHEN '1' THEN" +
            " IFNULL(t10.amount,0) " +
            " WHEN '2' THEN" +
            " IFNULL(t4.cost_price,0) * ( IFNULL(t10.percent,0) * 0.01 ) END ) addFee, " +
            " IFNULL( t4.cost_price, 0 ) cosPrice, " +
            " IFNULL( t5.service_charge_price, 0 ) servicePrice, " +
            " IFNULL( t5.other_fee_price, 0 )  taxPrice, " +
            " IFNULL( t4.cost_price, 0 ) + IFNULL( t5.service_charge_price, 0 ) + IFNULL( t5.other_fee_price, 0 ) AS price " +
            " FROM" +
            " t_ship_product t" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id" +
            " LEFT JOIN t_ship_product_sale_strategy t7 on t.id =t7.product_id" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.id = t10.offline_id " +
            " AND t.id = t10.product_id" +
            " left JOIN t_ship_product_sale_record t6 on t.id =t6.product_id and t10.relation_id = t6.custom_id" +
            " AND t9.channel_type = t6.channel " +
            " where t.is_deleted = 0 and t.is_recommend = 1 and t.shelves_status = 1 and t4.tourist_type_id = 1 and t9.visable_type in('2','3') and t9.channel_type =1 " +
            " <if test=\"localDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{localDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{localDate} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{localDate} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{localDate} " +
            " </if> " +
            " <if test=\"id != null and id != ''\"> AND t.id = #{id}</if> " +
            " <if test=\"userId != null \"> " +
            " and t10.relation_id in " +
            "<foreach collection='userId' item='id' open='(' separator=',' close=')'>"+
            "#{id}"+
            "</foreach>"+
            " </if>  " +
            "</script>")
    List<ShipProductCostAndDateListDTO> queryShipProductCostAndDateList2(@Param("id") Long id, @Param("localDate") LocalDate localDate,@Param("userId") List<String> userIds);
}
